package org.koyad.portal.context.listeners;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;

import org.koyad.portal.constants.FrameworkConstants;
import org.koyad.portal.db.PistonDBManager;
import org.koyad.portal.utils.FileUtils;
import org.koyad.portal.utils.LogUtils;

/**
 * @author koyad
 */

@WebListener
public class DBContextListener implements ServletContextListener {

	private static final LogUtils LOGGER = LogUtils
			.getLogger(DBContextListener.class);
	
	@Override
	public void contextInitialized(ServletContextEvent servletContextEvent) {

		LOGGER.startMethod("contextInitialized");
		
		Connection conn = null;
		Statement stmt = null;
		try {
			//This should not be done for java SE 6
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();

			conn = PistonDBManager.getConnection();
			conn.setAutoCommit(true);
			
			DatabaseMetaData metadata = conn.getMetaData();
			
			//check for marker table
			boolean markerTable = false;
			ResultSet rs = metadata.getTables(null, "APP", "MARKER", null);
			if(rs.next()) {
				markerTable = true;
				LOGGER.info("MARKER table is present");
			}
			rs.close();
			
			Set<String> files = null;
			ServletContext servCtx = servletContextEvent.getServletContext();
			if(!markerTable) {
				
				//get all tables
				rs = metadata.getTables(null, "APP", "%", null);
				Set<String> tables = new HashSet<String>();
				while(rs.next()) {
					String table = rs.getString(3);
					LOGGER.info("********** Table : " + rs.getString(3) + " **********");
					tables.add(table);
				}
				rs.close();
				
				if(null == files) {
					files = servCtx.getResourcePaths("/WEB-INF/classes/sql");
				}
				
				for(String file : files) {
					
					//check if table is present
					boolean tablePresent = false;
					//String tblName = "";
					for(String tableName : tables) {
						if(file.indexOf(tableName) > -1) {
							//tablePresent = true;
							//for testing only
							if(null == stmt) {
								stmt = conn.createStatement();
							}
							String dropQuery = "DROP TABLE " + tableName;
							LOGGER.info("Dropping table ..");
							LOGGER.info("DROP QUERY ==> " + dropQuery);
							stmt.executeUpdate(dropQuery);
							LOGGER.info("Table dropped.");
							
//							tblName = tableName;
							break;
						}
					}
					
					if(!tablePresent) {
						LOGGER.info("Creating table ..");
						LOGGER.info("Reading file - " + file);	
						
						//read sql file
						InputStream is = servCtx.getResourceAsStream(file);
						
						String createQuery = FileUtils.readFileAsString(is).replace("\t", " ");
						LOGGER.info("CREATE QUERY ==> " + createQuery);
						
						//execute query
						if(null == stmt) {
							stmt = conn.createStatement();
						}
						stmt.execute(createQuery);
						LOGGER.info("Table created.");
						
						if(file.indexOf(FrameworkConstants.TABLE_PAGE) > -1) {
							LOGGER.info("Uploading data to table '" + FrameworkConstants.TABLE_PAGE + "' ..");
							
							String updateQuery = "INSERT INTO PAGE (PAGE_NAME, PAGE_ID, PAGE_MAPPING, FRAME) VALUES ('Home', 'B01A5A657081445D842784F7B5DF066C', 'home', 'piston')";
							LOGGER.info("Executing query : " + updateQuery);
							stmt.executeUpdate(updateQuery);
							
//							String updateQuery1 = "INSERT INTO PAGE (PAGE_NAME, PAGE_ID, PAGE_MAPPING) VALUES ('Home', 'B02A5A657081445D842784F7B5DF066C', 'home')";
//							LOGGER.info("Executing query : " + updateQuery1);
//							stmt.executeUpdate(updateQuery1);
							
							String updateQuery2 = "INSERT INTO PAGE (PAGE_NAME, PAGE_ID, PAGE_MAPPING) VALUES ('Edit Page Layout', 'B03A5A657081445D842784F7B5DF066C', 'editPageLayout')";
							LOGGER.info("Executing query : " + updateQuery2);
							stmt.executeUpdate(updateQuery2);
							
//							String updateQuery3 = "INSERT INTO PAGE (PAGE_NAME, PAGE_ID, PAGE_MAPPING) VALUES ('Show Pages', 'B04A5A657081445D842784F7B5DF066C', 'showPages')";
//							LOGGER.info("Executing query : " + updateQuery3);
//							stmt.executeUpdate(updateQuery3);
							
							LOGGER.info("Data uploaded.");
						}
					}
				}
			}
		} catch (Exception e) {
//			LOGGER.debug("WARN : " + e.getMessage());
			LOGGER.logException(e);
			
		} finally {
			if(null != stmt) {
				try {
					stmt.close();
				} catch (SQLException e) {
					LOGGER.logException(e);
				}
			}
			if(null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					LOGGER.logException(e);
				}
			}
		}

		LOGGER.endMethod("contextInitialized");

	}
	
	@Override
	public void contextDestroyed(ServletContextEvent servletContextEvent) {

		LOGGER.startMethod("contextDestroyed");
		
		try {
			DriverManager.getConnection("jdbc:derby:piston;shutdown=true");
			
//			Enumeration<Driver> drivers = DriverManager.getDrivers();
//			while(drivers.hasMoreElements()) {
//				Driver driver = drivers.nextElement();
//				LOGGER.info("Unregistering driver : " + driver.getClass().getName());
//				DriverManager.deregisterDriver(driver);
//			}
			
		} catch (Exception e) {
			LOGGER.info(e.getMessage());
//			LOGGER.logException(e);
		}
		
		LOGGER.endMethod("contextDestroyed");
		
	}
	
}
